const express = require('express');
const mysql = require('mysql2');
const bodyParser = require('body-parser');
const cors = require('cors'); // 导入 cors 中间件

const app = express();
const port = 3000;

// 使用 body-parser 中间件来解析 JSON 请求
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));

// 启用 CORS 支持所有路由
app.use(cors());

// 创建 MySQL 连接
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '020502',
  database: 'hospital',
});

// 连接到 MySQL
connection.connect((err) => {
  if (err) {
    console.error('连接到 MySQL 时发生错误：', err);
  } else {
    console.log('已连接到 MySQL 数据库');
  }
});

// 定义用于 CRUD 操作的路由
app.get('/api/hospital', (req, res) => {
  // 从 MySQL 数据库获取医院信息
  connection.query('SELECT * FROM hospitalinfo', (error, results) => {
    if (error) {
      console.error('获取医院信息时发生错误：', error);
      res.status(500).json({ error: '内部服务器错误' });
    } else {
      res.json(results);
    }
  });
});

app.put('/api/hospital', (req, res) => {
  const updatedHospital = req.body;

  // 在 MySQL 数据库中更新医院信息
  connection.query(
    'UPDATE hospitalinfo SET name=?, address=?, phone=?, email=? WHERE id=?',
    [
      updatedHospital.name,
      updatedHospital.address,
      updatedHospital.phone,
      updatedHospital.email,
      updatedHospital.id,
    ],
    (error) => {
      if (error) {
        console.error('更新医院信息时发生错误：', error);
        res.status(500).json({ error: '内部服务器错误' });
      } else {
        res.json({ message: '医院信息更新成功' });
      }
    }
  );
});
// GET: Retrieve all departments
app.get('/api/departments', (req, res) => {
    connection.query('SELECT * FROM department', (error, results) => {
      if (error) {
        console.error('获取科室信息时发生错误：', error);
        res.status(500).json({ error: '内部服务器错误' });
      } else {
        res.json(results);
      }
    });
  });
  
  // GET: Retrieve a specific department by ID
  app.get('/api/departments/:id', (req, res) => {
    const departmentId = parseInt(req.params.id);
    connection.query('SELECT * FROM department WHERE id = ?', [departmentId], (error, results) => {
      if (error) {
        console.error('获取科室信息时发生错误：', error);
        res.status(500).json({ error: '内部服务器错误' });
      } else if (results.length === 0) {
        res.status(404).json({ error: '科室不存在' });
      } else {
        res.json(results[0]);
      }
    });
  });
  
  // POST: Create a new department
  app.post('/api/departments', (req, res) => {
    const newDepartment = req.body;
    connection.query('INSERT INTO department SET ?', newDepartment, (error, results) => {
      if (error) {
        console.error('创建科室时发生错误：', error);
        res.status(500).json({ error: '内部服务器错误' });
      } else {
        res.status(201).json({ id: results.insertId, ...newDepartment });
      }
    });
  });
  
  // PUT: Update a department by ID
  app.put('/api/departments/:id', (req, res) => {
    const departmentId = parseInt(req.params.id);
    const updatedDepartment = req.body;
    connection.query(
      'UPDATE department SET name=? WHERE id=?',
      [updatedDepartment.name, departmentId],
      (error) => {
        if (error) {
          console.error('更新科室信息时发生错误：', error);
          res.status(500).json({ error: '内部服务器错误' });
        } else {
          res.json({ message: '科室信息更新成功' });
        }
      }
    );
  });
  
  // DELETE: Delete a department by ID
  app.delete('/api/departments/:id', (req, res) => {
    const departmentId = parseInt(req.params.id);
    connection.query('DELETE FROM department WHERE id = ?', [departmentId], (error, results) => {
      if (error) {
        console.error('删除科室时发生错误：', error);
        res.status(500).json({ error: '内部服务器错误' });
      } else if (results.affectedRows === 0) {
        res.status(404).json({ error: '科室不存在' });
      } else {
        res.json({ message: '科室删除成功' });
      }
    });
  });

// 获取所有医生
app.get('/api/doctors', (req, res) => {
    connection.query('SELECT * FROM doctor', (error, results) => {
      if (error) {
        console.error('获取医生信息时发生错误：', error);
        res.status(500).json({ error: '内部服务器错误' });
      } else {
        res.json(results);
      }
    });
  });
  
  // 获取单个医生
  app.get('/api/doctors/:name', (req, res) => {
    const doctorId = req.params.name;
    connection.query('SELECT * FROM doctor WHERE name = ?', [doctorId], (error, results) => {
      if (error) {
        console.error('获取医生信息时发生错误：', error);
        res.status(500).json({ error: '内部服务器错误' });
      } else {
        if (results.length > 0) {
          res.json(results[0]);
        } else {
          res.status(404).json({ error: '医生不存在' });
        }
      }
    });
  });
  
  // 添加医生
  app.post('/api/doctors', (req, res) => {
    const newDoctor = req.body;
  
    connection.query(
      'INSERT INTO doctor (name, address, phone, age, sex, password, department) VALUES (?, ?, ?, ?, ?, ?, ?)',
      [newDoctor.name, newDoctor.address, newDoctor.phone, newDoctor.age, newDoctor.sex, newDoctor.password, newDoctor.department],
      (error) => {
        if (error) {
          console.error('添加医生时发生错误：', error);
          res.status(500).json({ error: '内部服务器错误' });
        } else {
          res.json({ message: '医生添加成功' });
        }
      }
    );
  });
  
  // 更新医生信息
  app.put('/api/doctors/:id', (req, res) => {
    const doctorId = req.params.id;
    const updatedDoctor = req.body;
  
    connection.query(
      'UPDATE doctor SET name=?, address=?, phone=?, age=?, sex=?, password=?, department=? WHERE id=?',
      [
        updatedDoctor.name,
        updatedDoctor.address,
        updatedDoctor.phone,
        updatedDoctor.age,
        updatedDoctor.sex,
        updatedDoctor.password,
        updatedDoctor.department,
        doctorId,
      ],
      (error) => {
        if (error) {
          console.error('更新医生信息时发生错误：', error);
          res.status(500).json({ error: '内部服务器错误' });
        } else {
          res.json({ message: '医生信息更新成功' });
        }
      }
    );
  });
  
  // 删除医生
  app.delete('/api/doctors/:id', (req, res) => {
    const doctorId = req.params.id;
  
    connection.query('DELETE FROM doctor WHERE id = ?', [doctorId], (error) => {
      if (error) {
        console.error('删除医生时发生错误：', error);
        res.status(500).json({ error: '内部服务器错误' });
      } else {
        res.json({ message: '医生删除成功' });
      }
    });
  });

// GET: 获取所有用户
app.get('/api/users', (req, res) => {
    connection.query('SELECT * FROM user', (error, results) => {
      if (error) {
        console.error('获取用户信息时发生错误：', error);
        res.status(500).json({ error: '内部服务器错误' });
      } else {
        res.json(results);
      }
    });
  });
  
  // GET: 获取特定 ID 的用户
  app.get('/api/users/:id', (req, res) => {
    const userId = req.params.id;
    connection.query('SELECT * FROM user WHERE id = ?', [userId], (error, results) => {
      if (error) {
        console.error('获取用户信息时发生错误：', error);
        res.status(500).json({ error: '内部服务器错误' });
      } else {
        if (results.length > 0) {
          res.json(results[0]);
        } else {
          res.status(404).json({ error: '用户不存在' });
        }
      }
    });
  });
  
  // POST: 创建新用户
  app.post('/api/users', (req, res) => {
    const newUser = req.body;
  
    connection.query(
      'INSERT INTO user (name, password, phone, sex, address) VALUES (?, ?, ?, ?, ?)',
      [newUser.name, newUser.password, newUser.phone, newUser.sex, newUser.address],
      (error, results) => {
        if (error) {
          console.error('添加用户时发生错误：', error);
          res.status(500).json({ error: '内部服务器错误' });
        } else {
          res.status(201).json({ id: results.insertId, ...newUser });
        }
      }
    );
  });
  
  // PUT: 根据 ID 更新用户信息
  app.put('/api/users/:id', (req, res) => {
    const userId = req.params.id;
    const updatedUser = req.body;
  
    connection.query(
      'UPDATE user SET name=?, password=?, phone=?, sex=?, address=? WHERE id=?',
      [updatedUser.name, updatedUser.password, updatedUser.phone, updatedUser.sex, updatedUser.address, userId],
      (error) => {
        if (error) {
          console.error('更新用户信息时发生错误：', error);
          res.status(500).json({ error: '内部服务器错误' });
        } else {
          res.json({ message: '用户信息更新成功' });
        }
      }
    );
  });
  
  // DELETE: 根据 ID 删除用户
  app.delete('/api/users/:id', (req, res) => {
    const userId = req.params.id;
  
    connection.query('DELETE FROM user WHERE id = ?', [userId], (error) => {
      if (error) {
        console.error('删除用户时发生错误：', error);
        res.status(500).json({ error: '内部服务器错误' });
      } else {
        res.json({ message: '用户删除成功' });
      }
    });
  });
// GET: 获取所有 addreser 记录
app.get('/api/addreser', (req, res) => {
    connection.query('SELECT * FROM addreser', (error, results) => {
      if (error) {
        console.error('获取 addreser 信息时发生错误：', error);
        res.status(500).json({ error: '内部服务器错误' });
      } else {
        res.json(results);
      }
    });
  });
  
  // GET: 获取特定 ID 的 addreser 记录
  app.get('/api/addreser/:id', (req, res) => {
    const addreserId = req.params.id;
    connection.query('SELECT * FROM addreser WHERE id = ?', [addreserId], (error, results) => {
      if (error) {
        console.error('获取 addreser 信息时发生错误：', error);
        res.status(500).json({ error: '内部服务器错误' });
      } else {
        if (results.length > 0) {
          res.json(results[0]);
        } else {
          res.status(404).json({ error: 'addreser 记录不存在' });
        }
      }
    });
  });
  
  // POST: 创建新 addreser 记录
  app.post('/api/addreser', (req, res) => {
    const newAddreser = req.body;
  
    connection.query(
      'INSERT INTO addreser (name, doctor, paymentAmount, phone, reservationTime, state, department) VALUES (?, ?, ?, ?, ?, ?, ?)',
      [
        newAddreser.name,
        newAddreser.doctor,
        newAddreser.paymentAmount,
        newAddreser.phone,
        newAddreser.reservationTime,
        newAddreser.state,
        newAddreser.department,
      ],
      (error, results) => {
        if (error) {
          console.error('添加 addreser 记录时发生错误：', error);
          res.status(500).json({ error: '内部服务器错误' });
        } else {
          res.status(201).json({ id: results.insertId, ...newAddreser });
        }
      }
    );
  });
  
  // PUT: 根据 ID 更新 addreser 记录
  app.put('/api/addreser/:id', (req, res) => {
    const addreserId = req.params.id;
    const updatedAddreser = req.body;
  
    connection.query(
      'UPDATE addreser SET name=?, doctor=?, paymentAmount=?, phone=?, reservationTime=?, state=?, department=? WHERE id=?',
      [
        updatedAddreser.name,
        updatedAddreser.doctor,
        updatedAddreser.paymentAmount,
        updatedAddreser.phone,
        updatedAddreser.reservationTime,
        updatedAddreser.state,
        updatedAddreser.department,
        addreserId,
      ],
      (error) => {
        if (error) {
          console.error('更新 addreser 记录时发生错误：', error);
          res.status(500).json({ error: '内部服务器错误' });
        } else {
          res.json({ message: 'addreser 记录更新成功' });
        }
      }
    );
  });
  
  // DELETE: 根据 ID 删除 addreser 记录
  app.delete('/api/addreser/:id', (req, res) => {
    const addreserId = req.params.id;
  
    connection.query('DELETE FROM addreser WHERE id = ?', [addreserId], (error) => {
      if (error) {
        console.error('删除 addreser 记录时发生错误：', error);
        res.status(500).json({ error: '内部服务器错误' });
      } else {
        res.json({ message: 'addreser 记录删除成功' });
      }
    });
  });
  // GET: 根据 name 列名查询所有 addreser 记录
app.get('/api/addreser/name/:name', (req, res) => {
    const nameValue = req.params.name;
    connection.query('SELECT * FROM addreser WHERE name = ?', [nameValue], (error, results) => {
      if (error) {
        console.error('根据 name 列名查询 addreser 信息时发生错误：', error);
        res.status(500).json({ error: '内部服务器错误' });
      } else {
        res.json(results);
      }
    });
  });
// GET: 根据 doctor 列名查询所有 addreser 记录
app.get('/api/addreser/doctor/:doctor', (req, res) => {
    const doctorValue = req.params.doctor;
    connection.query('SELECT * FROM addreser WHERE doctor = ?', [doctorValue], (error, results) => {
      if (error) {
        console.error('根据 doctor 列名查询 addreser 信息时发生错误：', error);
        res.status(500).json({ error: '内部服务器错误' });
      } else {
        res.json(results);
      }
    });
  });
// GET: 查询 doctor 表中所有记录的 department 信息
app.get('/api/doctors-departments', (req, res) => {
    connection.query('SELECT doctor.department AS department_name FROM doctor', (error, results) => {
      if (error) {
        console.error('查询 doctor 表中所有记录的 department 信息时发生错误：', error);
        res.status(500).json({ error: '内部服务器错误' });
      } else {
        // 使用 Set 数据结构来获取唯一的 department 名称
        const uniqueDepartments = [...new Set(results.map(result => result.department_name))];
        res.json(uniqueDepartments);
      }
    });
  });
// GET: 根据 department 查询对应的 doctor 姓名
app.get('/api/doctors-by-department/:department', (req, res) => {
    const departmentName = req.params.department;

    connection.query('SELECT name FROM doctor WHERE department = ?', [departmentName], (error, results) => {
        if (error) {
            console.error(`根据 department 查询对应的 doctor 时发生错误：${error}`);
            res.status(500).json({ error: '内部服务器错误' });
        } else {
            res.json(results);
        }
    });
});
// GET: 查询所有药物信息
app.get('/api/medications', (req, res) => {
    connection.query('SELECT name, price, number FROM medication', (error, results) => {
        if (error) {
            console.error(`查询所有药物信息时发生错误：${error}`);
            res.status(500).json({ error: '内部服务器错误' });
        } else {
            res.json(results);
        }
    });
});

// GET: Query all medication information
app.get('/api/examination', (req, res) => {
    const query = 'SELECT * FROM examination';
  
    connection.query(query, (error, results) => {
      if (error) {
        console.error(`Error querying medication information: ${error}`);
        res.status(500).json({ error: 'Internal Server Error' });
      } else {
        res.json(results);
      }
    });
  });

  // API endpoint to insert data into 'pay' table
app.post('/api/pay', (req, res) => {
    const { department, time, cases,examination, medication, examinationprice, medicationprice, total,doctor,name } = req.body;
  
    const sql = 'INSERT INTO pay (department, time, cases, examination, medication, examinationprice, medicationprice, total,doctor,name ) VALUES (?, ?, ?, ?, ?, ?, ?, ?,?,?)';
  
    const values = [department, time, cases, examination, medication, examinationprice, medicationprice, total,doctor,name];
  
    connection.query(sql, values, (error, results) => {
      if (error) {
        console.error('Error inserting data into pay table:', error);
        res.status(500).json({ error: 'Internal Server Error' });
      } else {
        console.log('Data inserted into pay table:', results);
        res.json({ message: 'Data inserted successfully' });
      }
    });
  });
  app.post('/api/register', (req, res) => {
    const { name, password, phone, sex, address,role } = req.body;
  
    connection.query(
      'INSERT INTO user (name, password, phone, sex, address,role) VALUES (?, ?, ?, ?, ?,?)',
      [name, password, phone, sex, address,role],
      (error, results) => {
        if (error) {
          console.error(`Error registering user: ${error}`);
          res.status(500).json({ error: 'Internal Server Error' });
        } else {
          res.json({ message: 'User registered successfully' });
        }
      }
    );
  });
  //登录
  app.post('/api/login', (req, res) => {
    const { name, password, role } = req.body;
  
    connection.query(
      'SELECT * FROM user WHERE name = ? AND password = ? AND role = ?',
      [name, password, role],
      (error, results) => {
        if (error) {
          console.error(`Error during login: ${error}`);
          res.status(500).json({ error: 'Internal Server Error' });
        } else {
          if (results.length > 0) {
            // User found, login successful
            res.json({ message: 'Login successful', user: results[0] });
          } else {
            // User not found or invalid credentials
            res.status(401).json({ error: 'Invalid credentials' });
          }
        }
      }
    );
  });
  // GET: 根据 name 查询表 pay 所有信息
app.get('/api/pay-by-name/:name', (req, res) => {
  const payName = req.params.name;

  connection.query('SELECT * FROM pay WHERE name = ?', [payName], (error, results) => {
      if (error) {
          console.error(`根据 name 查询表 pay 时发生错误：${error}`);
          res.status(500).json({ error: '内部服务器错误' });
      } else {
          res.json(results);
      }
  });
});
app.get('/api/paylist', (req, res) => {
  connection.query('SELECT * FROM paylist', (error, results) => {
      if (error) {
          console.error(`查询所有 paylist 记录时发生错误：${error}`);
          res.status(500).json({ error: '内部服务器错误' });
      } else {
          res.json(results);
      }
  });
});
app.post('/api/paylist', (req, res) => {
  const name = req.body.name;

  if (!name) {
    return res.status(400).json({ error: '缺少 name 参数' });
  }

  const query = 'SELECT * FROM paylist WHERE name = ?';

  connection.query(query, [name], (error, results) => {
    if (error) {
      console.error(`根据 name 查询 paylist 记录时发生错误：${error}`);
      res.status(500).json({ error: '内部服务器错误' });
    } else {
      res.json(results);
    }
  });
});
app.post('/api/paylistssss', (req, res) => {
  const name = req.body.name;

  if (!name) {
    return res.status(400).json({ error: '缺少 name 参数' });
  }

  const query = 'SELECT * FROM paylist WHERE doctor = ?';

  connection.query(query, [name], (error, results) => {
    if (error) {
      console.error(`根据 name 查询 paylist 记录时发生错误：${error}`);
      res.status(500).json({ error: '内部服务器错误' });
    } else {
      res.json(results);
    }
  });
});
// CREATE: 添加一条记录到 paylist
app.post('/api/paylistss', (req, res) => {
  const paylistData = req.body; // Assuming the request body contains the necessary data

  connection.query('INSERT INTO paylist SET ?', paylistData, (error, results) => {
      if (error) {
          console.error(`添加 paylist 记录时发生错误：${error}`);
          res.status(500).json({ error: '内部服务器错误' });
      } else {
          res.json({ message: '成功添加 paylist 记录', id: results.insertId });
      }
  });
});
// API endpoint to delete data from 'pay' table based on ID
app.delete('/api/pay/:id', (req, res) => {
  const payId = req.params.id;

  const sql = 'DELETE FROM pay WHERE id = ?';

  connection.query(sql, [payId], (error, results) => {
      if (error) {
          console.error(`Error deleting data from pay table with ID ${payId}:`, error);
          res.status(500).json({ error: 'Internal Server Error' });
      } else if (results.affectedRows === 0) {
          res.status(404).json({ error: 'Record not found' });
      } else {
          console.log(`Data deleted from pay table with ID ${payId}`);
          res.json({ message: 'Data deleted successfully' });
      }
  });
});
app.delete('/api/paylist/:id', (req, res) => {
  const paylistId = req.params.id;

  connection.query('DELETE FROM paylist WHERE id = ?', paylistId, (error, results) => {
      if (error) {
          console.error(`删除 paylist 记录时发生错误：${error}`);
          res.status(500).json({ error: '内部服务器错误' });
      } else {
          if (results.affectedRows > 0) {
              res.json({ message: '成功删除 paylist 记录', id: paylistId });
          } else {
              res.status(404).json({ message: '未找到相应的 paylist 记录' });
          }
      }
  });
});
// 假设你已经设置好了Express和数据库连接

// 查询所有记录
app.get('/api/medication', (req, res) => {
  connection.query('SELECT * FROM medication', (error, results) => {
      if (error) {
          console.error(`查询 medication 记录时发生错误：${error}`);
          res.status(500).json({ error: '内部服务器错误' });
      } else {
          res.json(results);
      }
  });
});

// 查询单个记录
app.get('/api/medication/:id', (req, res) => {
  const medicationId = req.params.id;

  connection.query('SELECT * FROM medication WHERE id = ?', medicationId, (error, results) => {
      if (error) {
          console.error(`查询 medication 记录时发生错误：${error}`);
          res.status(500).json({ error: '内部服务器错误' });
      } else {
          if (results.length > 0) {
              res.json(results[0]);
          } else {
              res.status(404).json({ message: '未找到相应的 medication 记录' });
          }
      }
  });
});

// 添加记录
app.post('/api/medication', (req, res) => {
  const medicationData = req.body;

  connection.query('INSERT INTO medication SET ?', medicationData, (error, results) => {
      if (error) {
          console.error(`添加 medication 记录时发生错误：${error}`);
          res.status(500).json({ error: '内部服务器错误' });
      } else {
          res.json({ message: '成功添加 medication 记录', id: results.insertId });
      }
  });
});

// 更新记录
app.put('/api/medication/:id', (req, res) => {
  const medicationId = req.params.id;
  const updatedMedicationData = req.body;

  connection.query('UPDATE medication SET ? WHERE id = ?', [updatedMedicationData, medicationId], (error) => {
      if (error) {
          console.error(`更新 medication 记录时发生错误：${error}`);
          res.status(500).json({ error: '内部服务器错误' });
      } else {
          res.json({ message: '成功更新 medication 记录', id: medicationId });
      }
  });
});

// 删除记录
app.delete('/api/medication/:id', (req, res) => {
  const medicationId = req.params.id;

  connection.query('DELETE FROM medication WHERE id = ?', medicationId, (error, results) => {
      if (error) {
          console.error(`删除 medication 记录时发生错误：${error}`);
          res.status(500).json({ error: '内部服务器错误' });
      } else {
          if (results.affectedRows > 0) {
              res.json({ message: '成功删除 medication 记录', id: medicationId });
          } else {
              res.status(404).json({ message: '未找到相应的 medication 记录' });
          }
      }
  });
});
// 登录接口
app.post('/api/logindoctor', (req, res) => {
  const { username, password } = req.body;

  if (!username || !password) {
    return res.status(400).json({ error: '用户名和密码不能为空' });
  }
    const query = 'SELECT * FROM doctor WHERE name = ? AND password = ?';
    connection.query(query, [username, password], (error, results) => {
     

      if (error) {
        return res.status(500).json({ error: '内部服务器错误' });
      }

      if (results.length > 0) {
        // 登录成功
        const user = results[0];
        return res.json({ message: '登录成功', user });
      } else {
        // 用户名或密码错误
        return res.status(401).json({ error: '用户名或密码错误' });
      }
    });
 
});
app.post('/api/decrease-medication', (req, res) => {
  const { name } = req.body;

  if (!name) {
    return res.status(400).json({ error: 'Missing medication name in the request body' });
  }

  // Update 'number' column for the specified medication name
  connection.query(
    'UPDATE medication SET number = number - 1 WHERE name = ?',
    [name],
    (error, results) => {
      if (error) {
        console.error('Error decreasing medication number:', error);
        res.status(500).json({ error: 'Internal Server Error' });
      } else {
        res.json({ success: true });
      }
    }
  );
});
app.post('/api/add-medication', (req, res) => {
  const { name } = req.body;

  if (!name) {
    return res.status(400).json({ error: 'Missing medication name in the request body' });
  }

  // Update 'number' column for the specified medication name
  connection.query(
    'UPDATE medication SET number = number + 1 WHERE name = ?',
    [name],
    (error, results) => {
      if (error) {
        console.error('Error decreasing medication number:', error);
        res.status(500).json({ error: 'Internal Server Error' });
      } else {
        res.json({ success: true });
      }
    }
  );
});
app.put('/api/update-user-state/:id', (req, res) => {
    const userId = req.params.id;

    connection.query('UPDATE addreser SET state = 0 WHERE id = ?', userId, (error, results) => {
        if (error) {
            console.error(`更新 User 记录的 state 时发生错误：${error}`);
            res.status(500).json({ error: '内部服务器错误' });
        } else {
            res.json({ message: '成功更新 User 记录的 state' });
        }
    });
});
// 启动 Express 服务器
app.listen(port, () => {
  console.log(`服务器运行在端口 ${port}`);
});
